import Database from 'better-sqlite3';
import path from 'path';
import bcrypt from 'bcryptjs';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

// 角色定义
interface Role {
  id?: number;
  code: string;
  name: string;
  description: string;
  permissions: string; // JSON字符串，存储权限配置
  created_at?: string;
  updated_at?: string;
}

// 用户定义
interface User {
  id?: number;
  username: string;
  password: string;
  email: string;
  full_name: string;
  role_id: number;
  is_active: boolean;
  created_at?: string;
  updated_at?: string;
}

function createUserRoleTables() {
  console.log('🚀 开始创建用户角色管理系统...');

  // 临时禁用外键约束
  db.exec('PRAGMA foreign_keys = OFF');

  // 创建角色表
  const createRolesTable = `
    CREATE TABLE IF NOT EXISTS roles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      code TEXT UNIQUE NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      permissions TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `;

  // 创建用户表
  const createUsersTable = `
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL,
      full_name TEXT NOT NULL,
      role_id INTEGER NOT NULL,
      is_active BOOLEAN DEFAULT 1,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (role_id) REFERENCES roles (id)
    )
  `;

  // 创建用户会话表
  const createSessionsTable = `
    CREATE TABLE IF NOT EXISTS user_sessions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      session_token TEXT UNIQUE NOT NULL,
      expires_at TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `;

  db.exec(createRolesTable);
  db.exec(createUsersTable);
  db.exec(createSessionsTable);

  console.log('✅ 数据库表创建完成');
}

function insertDefaultRoles() {
  console.log('📋 插入默认角色...');

  const roles: Omit<Role, 'id' | 'created_at' | 'updated_at'>[] = [
    {
      code: 'A',
      name: '系统管理员',
      description: '拥有所有权限的系统管理员',
      permissions: JSON.stringify({
        canEditAll: true,
        canManageUsers: true,
        canManageRoles: true,
        dataSourceAccess: ['P', 'E', 'M', 'O', 'C', 'R']
      })
    },
    {
      code: 'P',
      name: '采购人员',
      description: '负责设备采购相关数据录入',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['P'],
        editableFields: {
          base_params: ['制造厂家', '供货厂家', '型号', '规格', '额定功率', '额定电流', '额定转速', '装箱单号'],
          naming: ['设备编号', '设备标签'],
          position: ['采购批次', '供应商信息']
        }
      })
    },
    {
      code: 'E',
      name: '设计人员',
      description: '负责设计相关数据录入',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['E'],
        editableFields: {
          base_params: ['设备名称', '规格'],
          naming: ['设备名称', '功能描述'],
          position: ['安装位置', '系统分区', '楼层信息']
        }
      })
    },
    {
      code: 'M',
      name: '监测人员',
      description: '负责监测相关数据录入',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['M'],
        editableFields: {
          base_params: ['监测数据', '监测记录', '状态参数'],
          naming: ['监测点编号', '监测设备编号'],
          position: ['监测位置', '监测点位']
        }
      })
    },
    {
      code: 'C',
      name: '施工人员',
      description: '负责设备施工安装相关数据录入',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['C'],
        editableFields: {
          base_params: ['安装日期', '施工记录', '验收报告'],
          naming: ['施工编号'],
          position: ['安装位置']
        }
      })
    },
    {
      code: 'O',
      name: '建设人员',
      description: '负责建设相关数据录入',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['O'],
        editableFields: {
          base_params: ['建设状态', '调试参数', '验收数据'],
          naming: ['建设编号', '验收编号'],
          position: ['建设位置', '调试位置']
        }
      })
    },
    {
      code: 'R',
      name: '运维人员',
      description: '负责设备运维相关数据录入',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['R'],
        editableFields: {
          base_params: ['投运日期', '维护记录', '运行状态'],
          naming: ['运行编号'],
          position: ['运行位置']
        }
      })
    },
    {
      code: 'V',
      name: '查看人员',
      description: '只能查看数据，无编辑权限',
      permissions: JSON.stringify({
        canEditAll: false,
        canManageUsers: false,
        canManageRoles: false,
        dataSourceAccess: ['P', 'E', 'M', 'O'],
        editableFields: {}
      })
    }
  ];

  const insertRoleStmt = db.prepare(`
    INSERT OR REPLACE INTO roles (code, name, description, permissions)
    VALUES (?, ?, ?, ?)
  `);

  roles.forEach(role => {
    insertRoleStmt.run(role.code, role.name, role.description, role.permissions);
    console.log(`✅ 插入角色: ${role.name}`);
  });
}

function insertTestUsers() {
  console.log('👥 创建测试用户...');

  // 获取角色ID
  const getRoleId = (code: string): number => {
    const role = db.prepare('SELECT id FROM roles WHERE code = ?').get(code) as any;
    if (!role) {
      throw new Error(`角色 ${code} 不存在`);
    }
    return role.id;
  };

  const testUsers: Omit<User, 'id' | 'created_at' | 'updated_at'>[] = [
    {
      username: 'admin',
      password: bcrypt.hashSync('admin123', 10),
      email: 'admin@example.com',
      full_name: '系统管理员',
      role_id: getRoleId('A'),
      is_active: true
    },
    {
      username: 'procurement',
      password: bcrypt.hashSync('proc123', 10),
      email: 'procurement@example.com',
      full_name: '张采购',
      role_id: getRoleId('P'),
      is_active: true
    },
    {
      username: 'design',
      password: bcrypt.hashSync('design123', 10),
      email: 'design@example.com',
      full_name: '李设计',
      role_id: getRoleId('E'),
      is_active: true
    },
    {
      username: 'monitoring',
      password: bcrypt.hashSync('mon123', 10),
      email: 'monitoring@example.com',
      full_name: '王监测',
      role_id: getRoleId('M'),
      is_active: true
    },
    {
      username: 'construction',
      password: bcrypt.hashSync('const123', 10),
      email: 'construction@example.com',
      full_name: '刘施工',
      role_id: getRoleId('C'),
      is_active: true
    },
    {
      username: 'build',
      password: bcrypt.hashSync('build123', 10),
      email: 'build@example.com',
      full_name: '王建设',
      role_id: getRoleId('O'),
      is_active: true
    },
    {
      username: 'operations',
      password: bcrypt.hashSync('ops123', 10),
      email: 'operations@example.com',
      full_name: '赵运维',
      role_id: getRoleId('R'),
      is_active: true
    },
    {
      username: 'viewer',
      password: bcrypt.hashSync('view123', 10),
      email: 'viewer@example.com',
      full_name: '陈查看',
      role_id: getRoleId('V'),
      is_active: true
    }
  ];

  const insertUserStmt = db.prepare(`
    INSERT OR REPLACE INTO users (username, password, email, full_name, role_id, is_active)
    VALUES (?, ?, ?, ?, ?, ?)
  `);

  testUsers.forEach(user => {
    insertUserStmt.run(
      user.username,
      user.password,
      user.email,
      user.full_name,
      user.role_id,
      user.is_active ? 1 : 0
    );
    console.log(`✅ 创建测试用户: ${user.full_name} (${user.username})`);
  });
}

function createIndexes() {
  console.log('📊 创建索引...');

  const indexes = [
    'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)',
    'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)',
    'CREATE INDEX IF NOT EXISTS idx_users_role_id ON users(role_id)',
    'CREATE INDEX IF NOT EXISTS idx_sessions_token ON user_sessions(session_token)',
    'CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON user_sessions(user_id)',
    'CREATE INDEX IF NOT EXISTS idx_roles_code ON roles(code)'
  ];

  indexes.forEach(indexSQL => {
    db.exec(indexSQL);
  });

  console.log('✅ 索引创建完成');
}

async function main() {
  try {
    createUserRoleTables();
    insertDefaultRoles();
    insertTestUsers();
    createIndexes();

    // 重新启用外键约束
    db.exec('PRAGMA foreign_keys = ON');

    console.log('\n🎉 用户角色管理系统创建完成!');
    console.log('\n📋 测试账号信息:');
    console.log('┌─────────────┬──────────┬─────────────┬──────────────┬──────────┐');
    console.log('│ 用户名      │ 密码     │ 角色        │ 角色代码     │ 说明     │');
    console.log('├─────────────┼──────────┼─────────────┼──────────────┼──────────┤');
    console.log('│ admin       │ admin123 │ 系统管理员  │ A            │ 所有权限 │');
    console.log('│ procurement │ proc123  │ 采购人员    │ P            │ 采购数据 │');
    console.log('│ design      │ design123│ 设计人员    │ E            │ 设计数据 │');
    console.log('│ monitoring  │ mon123   │ 监测人员    │ M            │ 监测数据 │');
    console.log('│ construction│ const123 │ 施工人员    │ C            │ 施工数据 │');
    console.log('│ build       │ build123 │ 建设人员    │ O            │ 建设数据 │');
    console.log('│ operations  │ ops123   │ 运维人员    │ R            │ 运维数据 │');
    console.log('│ viewer      │ view123  │ 查看人员    │ V            │ 只读权限 │');
    console.log('└─────────────┴──────────┴─────────────┴──────────────┴──────────┘');

  } catch (error) {
    console.error('❌ 创建用户角色系统失败:', error);
    process.exit(1);
  } finally {
    db.close();
  }
}

main();
